package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import vo.Administrator;
import vo.Books;

public class FunctionDAO {

	public Administrator login(String username, String pwd) {
		String sql = "select * from Administrator a where a.userName=? and a.password=?";
		Connection con = Connect.getConnection();
		PreparedStatement pstmt;
		Administrator a = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, pwd);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()) {
				a = new Administrator(rs.getInt(1), rs.getString(2), rs.getString(3));
			}
			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return a;
	}

	public List<Books> all() {
		String sql = "select * from books";
		List<Books> list = new ArrayList<Books>();
		Connection con = Connect.getConnection();
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Books b = new Books(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
				list.add(b);
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public void add(String date, String name, String sm) {
		String sql = "insert into books(name, title, date) values(?, ?, ?)";	
		Connection con = Connect.getConnection();
		PreparedStatement pstmt;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, sm);
			pstmt.setString(3, date);
			pstmt.execute();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void delete(String name, String sm) {
		String sql = "delete from books where name = ? and title = ?";
		Connection con = Connect.getConnection();
		PreparedStatement pstmt;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, sm);
			pstmt.execute();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Books> fun(String name) {
		String sql = "select * from books where name = ?";
		Connection con = Connect.getConnection();
		PreparedStatement stmt;
		List<Books> s = new ArrayList<Books>();
		try {
			stmt = con.prepareStatement(sql);
			stmt.setString(1, name);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()) {
				Books b = new Books(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4));
				s.add(b);
			}
			rs.close();
			stmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}

}
